from datetime import datetime
import pymysql
from pymysql import Connection, connect


# 创建数据连接
def create_connect():
    """
    创建数据库连接，如果数据库不存在则创建
    :return:数据库连接对象
    """
    try:
        # 先尝试连接MySQL服务器（不指定数据库）
        temp_conn = connect(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            charset='utf8'
        )
        temp_cursor = temp_conn.cursor()

        # 检查数据库是否存在
        temp_cursor.execute("SHOW DATABASES LIKE 'db6'")
        if not temp_cursor.fetchone():
            # 创建数据库
            temp_cursor.execute("CREATE DATABASE db6 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            print("数据库db6创建成功")

        temp_cursor.close()
        temp_conn.close()

        # 连接新创建的数据库
        conn = connect(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            database='db6',
            charset='utf8',
            autocommit=True
        )
        return conn

    except Exception as e:
        print(f"连接数据库失败：'{e}'")
        return None

# 创建数据表并初始化
def create_table():
    """
    创建数据表并初始化日志类型数据
    :return:是否成功
    """
    conn = create_connect()
    if conn is None:
        return False
    cursor = conn.cursor()

    # 创建日志类型表
    create_type_sql = """
                      CREATE TABLE IF NOT EXISTS t_type(
                          id INT PRIMARY KEY AUTO_INCREMENT, 
                          type_name VARCHAR (20),
                          type_description VARCHAR (200),
                          type_level VARCHAR (20)) 
                      """

    # 创建日志记录表
    create_log_sql = """
                     CREATE TABLE IF NOT EXISTS t_log(
                         id INT PRIMARY KEY AUTO_INCREMENT, 
                         occurrence_time DATETIME,
                         log_type_id INT,
                         log_details TEXT, 
                         FOREIGN KEY (log_type_id) REFERENCES t_type(id) ON DELETE CASCADE)
                     """
    try:
        cursor.execute(create_type_sql)  # 创建日志类型表
        cursor.execute(create_log_sql)  # 创建日志记录表

        # 检查是否存在数据
        cursor.execute("SELECT COUNT(*) FROM t_type")
        if cursor.fetchone()[0] == 0:
            # 初始化日志类型数据
            log_types = [
                ('ERROR', '系统错误', '高'),
                ('WARN', '警告信息', '中'),
                ('INFO', '普通信息', '低'),
                ('DEBUG', '调试日志', '低'),
                ('CRITICAL', '严重故障', '极高')
            ]

            insert_type_sql = "INSERT INTO t_type (type_name, type_description, type_level) VALUES (%s, %s, %s)"
            for type_name, type_description, type_level in log_types:
                cursor.execute(insert_type_sql, (type_name, type_description, type_level))
            print("日志类型数据初始化完成")

        conn.commit()
        print('数据表创建完成')
        return True

    except Exception as e:
        print(f"创建表失败: {e}")
        conn.rollback()
        return False
    finally:
        cursor.close()
        conn.close()


def import_log_data():
    """
    导入日志记录数据
    :return: None
    """
    conn = create_connect()
    if conn is None:
        return

    cursor = conn.cursor()

    try:
        # 读取文件
        with open('server_logs.txt', 'r', encoding='utf-8') as f:
            lines = f.readlines()

        # 处理每一行数据
        for line in lines:
            line = line.strip()
            if not line:
                continue

            parts = line.split(',', 2)  # 最多分割成3部分
            if len(parts) < 3:
                print(f"数据格式错误: {line}")
                continue

            # 解析时间
            try:
                occur_time = datetime.strptime(parts[0], "%Y-%m-%d %H:%M:%S")
            except ValueError:
                print(f"时间格式错误: {parts[0]}")
                continue

            # 获取日志类型ID
            type_name = parts[1]
            cursor.execute("SELECT id FROM t_type WHERE type_name = %s", (type_name,))
            type_result = cursor.fetchone()

            if type_result is None:
                print(f"未找到日志类型: {type_name}")
                continue

            type_id = type_result[0]
            log_details = parts[2]

            # 插入日志记录
            insert_sql = "INSERT INTO t_log (occurrence_time, log_type_id, log_details) VALUES (%s, %s, %s)"
            cursor.execute(insert_sql, (occur_time, type_id, log_details))

        conn.commit()
        print("日志数据导入完成")

    except Exception as e:
        print(f"导入日志数据失败: {e}")
        conn.rollback()
    finally:
        cursor.close()
        conn.close()


# 1.浏览所有日志记录
def all_logs():
    """
    查询并显示所有日志记录
    :return:查询结果
    """
    conn = create_connect()
    if conn is None:
        return

    cursor = conn.cursor()

    sql = """
          SELECT l.id, l.occurrence_time, t.type_name, t.type_level, l.log_details
          FROM t_log l
                   JOIN t_type t ON l.log_type_id = t.id
          ORDER BY l.occurrence_time DESC
          """
    cursor.execute(sql)
    result = cursor.fetchall()

    print("\n===== 所有日志记录 =====")
    print("ID\t发生时间\t\t\t类型\t级别\t详情")
    print("-" * 80)
    for row in result:
        # 显示日志详情前50和字符，过长添加省略号
        print(f"{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4][:50]}"
              f"{'...' if len(row[4]) > 50 else ''}")

    cursor.close()
    conn.close()
    return result


# 2.添加日志记录
def add_log_record():
    """
    添加新的日志记录
    :return: None
    """
    print("\n====== 添加日志记录 =======")

    # 获取时间
    occur_time_str = input("请输入发生时间（格式：YYYY-MM-DD HH:MM:SS）:")
    try:
        occur_time = datetime.strptime(occur_time_str, "%Y-%m-%d %H:%M:%S")
    except ValueError:
        print("时间格式错误！")
        return

    # 获取日志详情
    log_detail = input("请输入日志详情：")

    # 显示日志类型供选择
    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()
    cursor.execute("SELECT id, type_name, type_description, type_level FROM t_type")
    log_types = cursor.fetchall()

    print("\n可选日志类型:")
    for type_id, type_name, type_desc, type_level in log_types:
        print(f"{type_id}. {type_name} - {type_desc} ({type_level})")

    try:
        log_type_id = int(input("请输入日志类型ID："))
    except ValueError:
        print("日志类型ID格式错误!")
        conn.close()
        return

    # 检查选择的日志类型是否存在
    valid_type_ids = [t[0] for t in log_types]
    if log_type_id not in valid_type_ids:
        print("无效的日志类型ID!")
        conn.close()
        return

    # 插入日志记录
    sql = "INSERT INTO t_log (occurrence_time, log_type_id, log_details) VALUES (%s, %s, %s)"
    cursor.execute(sql, (occur_time, log_type_id, log_detail))

    if cursor.rowcount > 0:
        print("日志记录添加成功!")
    else:
        print("日志记录添加失败!")

    cursor.close()
    conn.close()


# 3.删除日志记录
def delete_log_record():
    """
    删除指定日志记录
    :return: None
    """
    print("\n======== 删除日志记录 ========")
    try:
        log_id = int(input("请输入要删除的日志ID："))
    except ValueError:
        print("日志ID格式错误!")
        return

    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()

    # 检查日志是否存在
    cursor.execute("SELECT id FROM t_log WHERE id = %s", (log_id,))
    if cursor.fetchone() is None:
        print("未找到该日志!")
        cursor.close()
        conn.close()
        return

    # 删除日志记录
    sql = "DELETE FROM t_log WHERE id = %s"
    cursor.execute(sql, (log_id,))

    if cursor.rowcount > 0:
        print("日志记录删除成功!")
    else:
        print("日志记录删除失败!")

    cursor.close()
    conn.close()


# 4.查询某时间段内的所有日志
def query_logs_by_time_range():
    """
    查询某时间段内的所有日志
    :return: None
    """
    print("\n======== 按时间段查询日志 ========")
    start_time_str = input("请输入开始时间（格式：YYYY-MM-DD HH:MM:SS）: ")
    end_time_str = input("请输入结束时间（格式：YYYY-MM-DD HH:MM:SS）: ")

    try:
        start_time = datetime.strptime(start_time_str, "%Y-%m-%d %H:%M:%S")
        end_time = datetime.strptime(end_time_str, "%Y-%m-%d %H:%M:%S")
    except ValueError:
        print("时间格式错误！")
        return

    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()

    # 查询指定时间段内的日志
    sql = """
          SELECT l.id, l.occurrence_time, t.type_name, t.type_level, l.log_details
          FROM t_log l
                   JOIN t_type t ON l.log_type_id = t.id
          WHERE l.occurrence_time BETWEEN %s AND %s
          ORDER BY l.occurrence_time
          """
    cursor.execute(sql, (start_time, end_time))
    result = cursor.fetchall()

    print(f"\n在 {start_time} 到 {end_time} 时间段内的日志记录:")
    print("ID\t发生时间\t\t\t类型\t级别\t详情")
    print("-" * 80)
    for row in result:
        print(f"{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4][:50]}"
              f"{'...' if len(row[4]) > 50 else ''}")

    cursor.close()
    conn.close()


# 5.查看严重级别为"极高"或"高"的日志
def query_high_level_logs():
    """
    查看严重级别为"极高"或"高"的日志
    :return: 查询结果
    """
    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()

    sql = """
          SELECT l.id, l.occurrence_time, t.type_name, t.type_level, l.log_details
          FROM t_log l
                   JOIN t_type t ON l.log_type_id = t.id
          WHERE t.type_level IN ('极高', '高')
          ORDER BY l.occurrence_time DESC
          """
    cursor.execute(sql)
    result = cursor.fetchall()

    print("\n===== 严重级别为'极高'或'高'的日志 =======")
    print("ID\t发生时间\t\t\t类型\t级别\t详情")
    print("-" * 80)
    for row in result:
        print(f"{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4][:50]}"
              f"{'...' if len(row[4]) > 50 else ''}")

    cursor.close()
    conn.close()
    return result


# 6.找出TOP3最频繁出现的错误消息
def find_top3_error_messages():
    """
    查询出现频率最高的3条错误
    :return:查询结果
    """
    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()

    sql = """
          SELECT log_details, COUNT(*) as count
          FROM t_log l
              JOIN t_type t
          ON l.log_type_id = t.id
          WHERE t.type_level IN ('高', '极高')
          GROUP BY log_details
          ORDER BY count DESC
              LIMIT 3
          """
    cursor.execute(sql)
    result = cursor.fetchall()

    print("\n======== TOP3 最频繁出现的错误消息 ========")
    for i, (log_detail, count) in enumerate(result, 1):
        print(f"{i}. 出现次数: {count}, 错误消息: {log_detail[:100]}"
              f"{'...' if len(log_detail) > 100 else ''}")

    cursor.close()
    conn.close()
    return result


# 7.按级别统计日志数量
def count_logs_by_level():
    """
    按日志级别统计日志数量
    :return: 统计结果
    """
    conn = create_connect()
    if conn is None:
        return
    cursor = conn.cursor()

    sql = """
          SELECT t.type_level, t.type_name, COUNT(*) as count
          FROM t_log l
              JOIN t_type t
          ON l.log_type_id = t.id
          GROUP BY t.type_level, t.type_name
          ORDER BY
              CASE t.type_level
              WHEN '极高' THEN 1
              WHEN '高' THEN 2
              WHEN '中' THEN 3
              WHEN '低' THEN 4
              ELSE 5
          END
          """
    cursor.execute(sql)
    result = cursor.fetchall()

    print("\n======== 按级别统计日志数量 ========")
    for type_level, type_name, count in result:
        print(f"{type_level}[{type_name}] {count} 条")

    cursor.close()
    conn.close()
    return result


# 主菜单
def main_menu():
    """
    显示主菜单 用户选择
    :return:
    """
    while True:
        print("\n======== 日志管理系统 ========")
        print("1. 浏览所有日志记录")
        print("2. 添加日志记录")
        print("3. 删除日志记录")
        print("4. 查询某时间段内的所有日志")
        print("5. 查看严重级别为'极高'或'高'的日志")
        print("6. 找出TOP3最频繁出现的错误消息")
        print("7. 按级别统计日志数量")
        print("8. 退出系统")

        choice = input("请选择操作: ")

        if choice == '1':
            all_logs()
        elif choice == '2':
            add_log_record()
        elif choice == '3':
            delete_log_record()
        elif choice == '4':
            query_logs_by_time_range()
        elif choice == '5':
            query_high_level_logs()
        elif choice == '6':
            find_top3_error_messages()
        elif choice == '7':
            count_logs_by_level()
        elif choice == '8':
            print("感谢使用日志管理系统，再见!")
            break
        else:
            print("无效选择，请重新输入!")


if __name__ == '__main__':
    # 初始化数据库表
    if create_table():
        # 检查是否需要导入数据
        conn = create_connect()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM t_log")
            count = cursor.fetchone()[0]
            cursor.close()
            conn.close()

            if count == 0:
                print("检测到日志记录表为空，开始导入初始数据...")
                import_log_data()

        # 运行主菜单
        main_menu()
    else:
        print("系统初始化失败，请检查数据库连接!")


